﻿using Model;
using MySql.Data;
using MySql.Data.MySqlClient;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace DAL
{
    public class DALPost
    {
        public Model.Post post;
        MySqlConnection mysql;
        DoSQL doSql = new DoSQL();
        public DALPost(Post post)
        {
            mysql = doSql.mysql;
            this.post = post;
        }

        public int delpost(string postid)
        {
            string sql = "delete from posts where id=" + postid;
            doSql.openmysql();
            MySqlCommand comm = new MySqlCommand(sql, mysql);
            int result = comm.ExecuteNonQuery();
            doSql.closemysql();
            return result;
        }
        public int getminpost()
        {
            string sql = "select min(id) from posts";
            doSql.openmysql();
            MySqlCommand comm = new MySqlCommand(sql, mysql);
            MySqlDataReader reader = comm.ExecuteReader();
            while (reader.Read())
            {
                if (reader.HasRows)
                {
                    int maxid = reader.GetInt32(0);
                    reader.Close();
                    doSql.closemysql();
                    return maxid;
                }

            }
            reader.Close();
            doSql.closemysql();
            return 0;
        }

        public int modifypost(Model.Post psot)
        {
            string sql = string.Format("update posts set postname='{0}',postcontent='{1}',postdate='{2}',postuser='{3}' where id={4}", post.postname, post.postcontent, post.posttime, post.postusername, post.postid);
            doSql.openmysql();
            MySqlCommand comm = new MySqlCommand(sql, mysql);
            return comm.ExecuteNonQuery();
        }

        public int getmaxpost()
        {
            string sql = "select id from posts order by id desc";
            doSql.openmysql();
            MySqlCommand comm = new MySqlCommand(sql, mysql);
            if (mysql.State == ConnectionState.Closed)
            {
                mysql.Open();
            }
            MySqlDataReader reader = comm.ExecuteReader();

            while (reader.Read())
            {
                if (reader.HasRows)
                {
                    int maxid = reader.GetInt32(0);
                    reader.Close();
                    doSql.closemysql();
                    return maxid;
                }

            }
            reader.Close();
            doSql.closemysql();
            return 0;

        }


        public Dictionary<string, string> GetThePost(int id)
        {
            Dictionary<string, string> MyPost = new Dictionary<string, string>();

            if (!checkpostid(id))
            {
                return null;
            }

            doSql.openmysql();
            string sql = "select * from posts where posts.id=" + id;
            MySqlCommand comm = new MySqlCommand(sql, mysql);
            MySqlDataReader reader = comm.ExecuteReader();
            while (reader.Read())
            {
                if (reader.HasRows)
                {
                    string postid = Convert.ToString(reader.GetInt32(0));
                    string postname = reader.GetString(1);
                    string postcontent = reader.GetString(2);
                    string postdate = reader.GetString(3);
                    string postuser = reader.GetString(4);

                    MyPost.Add("postid", postid);
                    MyPost.Add("postname", postname);
                    MyPost.Add("postcontent", postcontent);
                    MyPost.Add("postdate", postdate);
                    MyPost.Add("postuser", postuser);
                }
            }
            doSql.closemysql();
            return MyPost;
        }

        public bool checkpostid(int id)
        {
            doSql.openmysql();
            string sql = "select * from posts where posts.id=" + id.ToString();
            MySqlCommand comm = new MySqlCommand(sql, mysql);
            MySqlDataReader reader = comm.ExecuteReader();
            reader.Read();
            if (reader.HasRows)
            {
                doSql.closemysql();
                return true;
            }
            else
            {
                doSql.closemysql();
                return false;
            }
        }
        public List<Model.Post> searchlist(string strquery)
        {
            List<Model.Post> result = new List<Model.Post>();
            doSql.openmysql();
            //string sql = string.Format("select * from posts where postname like '\%{0}\%' or postcontent like '\%{1}\%' ", strquery, strquery);
            string sql="select * from posts where postname like '%"+strquery+"%' or postcontent like '%"+strquery+"%' ";

            MySqlCommand comm = new MySqlCommand(sql, mysql);
            MySqlDataReader reader = comm.ExecuteReader();
            while (reader.Read())
            {
                if (reader.HasRows)
                {
                    string postid = Convert.ToString(reader.GetInt32(0));
                    string postname = reader.GetString(1);
                    string postcontent = reader.GetString(2);
                    string postdate = reader.GetString(3);
                    string postuser = reader.GetString(4);

                    Model.Post mypost = new Model.Post(postid, postname, postcontent, postdate, postuser);
                    result.Add(mypost);
                }
                else
                {
                    return null;
                }
            }
            doSql.closemysql();
            return result;
        }



        public void insertintoposts()
        {
            doSql.openmysql();
            Random ra = new Random();
            DateTime time = DateTime.Now;
            string strnowtime = time.ToString();
            for (int i = 1; i <= 100; i++)
            {
                int nextval = ra.Next(10);
                string postname = "";
                string postcontent = "";
                for (int j = 0; j < nextval; j++)
                {
                    postname += "postname  ";
                    postcontent += "postcontent  ";
                }
                string sql =
                    string.Format("insert into posts(id,postname,postcontent,postdate,postuser) values ('{0}','{1}','{2}','{3}','{4}');",
                   i, postname, postcontent, time, "fff");
                MySqlCommand comm = new MySqlCommand(sql, mysql);
                comm.ExecuteNonQuery();
            }
            doSql.closemysql();
        }



        public bool insertpost()
        {
            try
            {
                doSql.openmysql();
                string sql =
                    string.Format("insert into posts(postname,postcontent,postdate,postuser) values ('{0}','{1}','{2}','{3}');",
                    post.postname, post.postcontent, post.posttime, post.postusername);
                MySqlCommand comm = new MySqlCommand(sql, mysql);
                comm.ExecuteNonQuery();
                doSql.closemysql();
                return true;
            }
            catch (Exception)
            {
                doSql.closemysql();
                return false;
            }
        }
    }
}
